﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace WordWonderGame.Business
{
    class DataClassDAL
    {
        private string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=WWGdatabase.accdb";
        public DataClassDAL()
        {
        }

        public CauHoiMan4 LayCauHoiMan4()
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                Dictionary<string, string> listCauHoi = new Dictionary<string, string>();
                OleDbCommand command = new OleDbCommand("select * from tblDIAPHUONG", con);
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                DataTable dtDiaPhuong = new DataTable();
                da.Fill(dtDiaPhuong);
                foreach (DataRow dtr in dtDiaPhuong.Rows)
                {
                    listCauHoi.Add(dtr[1].ToString(), dtr[0].ToString());
                }
                List<string> KeysOflistCauHoi = listCauHoi.Keys.ToList();
                KeysOflistCauHoi = KeysOflistCauHoi.OrderBy(x => Guid.NewGuid()).Take(8).ToList();
                Dictionary<string, string> listCauHoi2 = new Dictionary<string, string>();
                foreach (string s in KeysOflistCauHoi)
                {
                    listCauHoi2.Add(s, listCauHoi[s]);
                }
                CauHoiMan4 cauhoiMan4 = new CauHoiMan4(listCauHoi2);
                return cauhoiMan4;
            }
        }

        public List<CauHoiMan3> Lay7CauHoiMan3()
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                List<CauHoiMan3> listCauHoi = new List<CauHoiMan3>();

                OleDbCommand command = new OleDbCommand("select tu from tblVAYMUON", con);
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                DataTable dtTuMuon = new DataTable();
                da.Fill(dtTuMuon);
                List<string> listTuMuon = new List<string>();
                foreach (DataRow dtr in dtTuMuon.Rows)
                {
                    listTuMuon.Add(dtr[0].ToString());
                }
                listTuMuon = listTuMuon.OrderBy(x => Guid.NewGuid()).Take(28).ToList();

                command = new OleDbCommand("select tu from tblDONGNGHIA", con);
                da.SelectCommand = command;
                DataTable dtTuThuong = new DataTable();
                da.Fill(dtTuThuong);
                List<string> listTuThuong = new List<string>();
                foreach (DataRow dtr in dtTuThuong.Rows)
                {
                    listTuThuong.Add(dtr[0].ToString());
                }

                listTuThuong = listTuThuong.OrderBy(x => Guid.NewGuid()).Take(14).ToList();
                for (int i = 0; i < 7; i++)
                {
                    List<string> c = new List<string>();
                    c.Add(listTuMuon[i * 4]);
                    c.Add(listTuMuon[i * 4 + 1]);
                    c.Add(listTuMuon[i * 4 + 2]);
                    c.Add(listTuMuon[i * 4 + 3]);
                    c.Add(listTuThuong[i * 2]);
                    c.Add(listTuThuong[i * 2 + 1]);
                    CauHoiMan3 c3 = new CauHoiMan3(c);
                    listCauHoi.Add(c3);
                }

                return listCauHoi;
            }
        }

        public List<CauHoiMan1> Lay7CauHoiMan1()
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                List<CauHoiMan1> cauhoi = new List<CauHoiMan1>();
                OleDbCommand command = new OleDbCommand("select max(nhom) from tblDONGNGHIA", con);
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                DataTable dtMaxNhom = new DataTable();
                da.Fill(dtMaxNhom);
                int maxNhom = int.Parse(dtMaxNhom.Rows[0][0].ToString());
                List<int> thuTuCau = new List<int>();
                for (int i = 1; i <= maxNhom; i++)
                {
                    thuTuCau.Add(i);
                }
                thuTuCau = thuTuCau.OrderBy(x => Guid.NewGuid()).ToList();
                for (int i = 1; i <= 7; i++)
                {
                    Random rSai = new Random();
                    int randomSai = rSai.Next(1, thuTuCau.Count + 1);

                    while (randomSai == thuTuCau[i])
                    {
                        randomSai = rSai.Next(1, thuTuCau.Count + 1);
                    }
                    cauhoi.Add(Lay1CauHoiMan1(thuTuCau[i], randomSai));
                }

                return cauhoi;
            }
        }
        public CauHoiMan1 Lay1CauHoiMan1(int indexDapAn, int indexDaSai)
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                OleDbCommand command = new OleDbCommand("select * from tblDONGNGHIA where nhom =" + indexDapAn.ToString(), con);
                DataTable dtDapAn = new DataTable();
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                da.Fill(dtDapAn);
                Random rDapAn = new Random();
                string d1 = dtDapAn.Rows[rDapAn.Next(0, dtDapAn.Rows.Count)]["tu"].ToString();
                DataTable dtDaSai = new DataTable();
                da.SelectCommand = new OleDbCommand("select * from tblDONGNGHIA where nhom =" + indexDaSai.ToString(), con);
                da.Fill(dtDaSai);
                List<string> sai = new List<string>();
                foreach (DataRow dtr in dtDaSai.Rows)
                {
                    sai.Add(dtr["tu"].ToString());
                }

                Random rSai = new Random();
                int d2 = rSai.Next(0, dtDaSai.Rows.Count);
                string da2 = sai[d2];
                sai.RemoveAt(d2);

                int d3 = rSai.Next(0, dtDaSai.Rows.Count - 1);
                string da3 = sai[d3];
                sai.RemoveAt(d3);

                int d4 = rSai.Next(0, dtDaSai.Rows.Count - 2);
                string da4 = sai[d4];
                sai.RemoveAt(d4);

                CauHoiMan1 cauhoi = new CauHoiMan1(d1, da2, da3, da4);
                return cauhoi;

            }
        }

        public CauHoiMan2 LayCauHoiMan2()
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                OleDbCommand command = new OleDbCommand("select max(nhom) from tblTRAINGHIA", con);
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                DataTable dtMaxNhom = new DataTable();
                da.Fill(dtMaxNhom);
                int maxNhom = int.Parse(dtMaxNhom.Rows[0][0].ToString());
                List<int> ChonNhomRandom = new List<int>();
                for (int i = 1; i <= maxNhom; i++)
                {
                    ChonNhomRandom.Add(i);
                }
                ChonNhomRandom = ChonNhomRandom.OrderBy(x => Guid.NewGuid()).Take(8).ToList();
                List<string> ListDapAn = new List<string>();
                List<string> ListGoiY = new List<string>();
                foreach (int index in ChonNhomRandom)
                {
                    string dapAn = "";
                    string goiY = "";
                    LayRa1CapTu(index, ref dapAn, ref goiY);
                    Random ran = new Random();
                    int random = ran.Next(0, 2);
                    if (random == 0)
                    {
                        ListDapAn.Add(dapAn);
                        ListGoiY.Add(goiY);
                    }
                    else
                    {
                        ListDapAn.Add(goiY);
                        ListGoiY.Add(dapAn);
                    }
                }
                CauHoiMan2 cm2 = new CauHoiMan2(ListDapAn, ListGoiY);
                return cm2;
            }
        }
        public void LayRa1CapTu(int nhom, ref string tu1, ref string tu2)
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                OleDbCommand command = new OleDbCommand("select * from tblTRAINGHIA where nhom =" + nhom, con);
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                DataTable dtTu1 = new DataTable();
                da.Fill(dtTu1);
                List<string> listTu1 = new List<string>();
                foreach (DataRow dtr in dtTu1.Rows)
                {
                    listTu1.Add(dtr[0].ToString());
                }
                tu1 = listTu1.OrderBy(x => Guid.NewGuid()).ToList()[0];
                da.SelectCommand = new OleDbCommand("select * from tblTRAINGHIA where nhom =" + (0 - nhom), con);
                DataTable dtTu2 = new DataTable();
                da.Fill(dtTu2);
                List<string> listTu2 = new List<string>();
                foreach (DataRow dtr in dtTu2.Rows)
                {
                    listTu2.Add(dtr[0].ToString());
                }
                tu2 = listTu2.OrderBy(x => Guid.NewGuid()).ToList()[0];

            }
        }

        public void LuuDiem(string ten , string diem)
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                OleDbCommand command = new OleDbCommand("insert into tblDIEMCAO values ('"+ten+"',"+diem+",'"+DateTime.Now+"')",con);
                command.ExecuteNonQuery();

            }
        }

        public DataTable LayDiemSoCao()
        {
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                OleDbCommand command = new OleDbCommand("select * from tblDIEMCAO order by Diem desc", con);
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                DataTable dtBangDiem = new DataTable();
                da.Fill(dtBangDiem);
                return dtBangDiem;

            }
        }
    }
}
